﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Text;
using Mk.CommonMethod;
using System.Data;

namespace Dashboard.ProductBonus.Business
{
    public class BusinessBase
    {
        protected readonly DBConnection DbConnection;
        public BusinessBase()
        {
            DbConnection = new DBConnection
                                {
                                    ConnectionString = ConfigurationManager.AppSettings["ConnectionString"],
                                    ConnectionType = ConfigurationManager.AppSettings["ConnectionType"]
                                };
        }

        protected string FormatStringForSqlVariable(string inputString)
        {
            if (!string.IsNullOrEmpty(inputString))
            {
                return inputString.Replace("'", "''");
            }

            return "";
        }

        public DataTable GetAllReasonList()
        {
            string sql = "SELECT ptype,pcode,pdesc,pvalue1,pvalue2 FROM NonworkingReason WHERE ptype='ReasonCode' ORDER BY convert(int,pcode,0)";

            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable GetReasonInfo(string reasonCode)
        {
            string sql = "SELECT * FROM NonworkingReason WHERE ptype='ReasonCode' AND pcode='" + this.FormatStringForSqlVariable(reasonCode) + "'";

            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable GetStaffOfTeam(string team)
        {
            string sql = "select * from namelist where team='" + team + "' order by shift,staffno";
            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable GetStaffOfTeamShift(string team, string shift)
        {
            string sql = "select * from namelist where team='" + team + "' and shift='" + shift + "' order by staffno";
            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable LoadWorkingShift()
        {
            string sql = "select id,code,description from WorkingShift where IsValid='Y' order by id";
            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable LoadTeamList()
        {
            string sql = "select distinct team from namelist order by team";
            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable LoadTeamShiftList(string team)
        {
            string sql = "select distinct shift from namelist where team='" + team + "' order by shift";
            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable GetStaffInfo(string staffNo)
        {
            string sql = "select * from namelist where staffno='" + this.FormatStringForSqlVariable(staffNo) + "'";
            return DbConnection.DataSet(sql).Tables[0];
        }
    }
}
